# 创建数据库
CREATE SCHEMA SsmTestDB;
#使用数据库
USE SsmTestDB;
#创建表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id`       INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `email`    VARCHAR(255) NOT NULL COMMENT '用户邮箱',
  `password` VARCHAR(255) NOT NULL COMMENT '用户密码',
  `user_name` VARCHAR(255) COMMENT '用户昵称',
  `role`     VARCHAR(255) DEFAULT 'normal' COMMENT '用户身份',
  `status`   INT(1)  DEFAULT 0 COMMENT '用户状态',
  `register_time`  DATETIME  NOT NULL  DEFAULT NOW() COMMENT '注册时间',
  `register_ip`    VARCHAR(255)   COMMENT '注册IP',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`) USING BTREE
)
  ENGINE = InnoDB
  AUTO_INCREMENT = 2
  DEFAULT CHARSET = utf8;
#外键约束
SET FOREIGN_KEY_CHECKS = 1;
#插入数据
BEGIN ;
INSERT INTO `user` (email, password, user_name, register_ip)
VALUES ( 'xxx', 'xxxxx', 'xxxxx', '127.0.0.1')
  , ( '2213384507@qq.com', '123', '张三', '127.0.0.1')
  ,( '2213384501@qq.com', '456', '李四',  '127.0.0.1')
  ,( '2213384504@qq.com', '456', '李四',  '127.0.0.1')
  , ( '2213384505@qq.com', '456', '李四', '127.0.0.1');
COMMIT ;
#查询数据
SELECT * FROM user;
SELECT * FROM user WHERE id=1;
#OR
SELECT * FROM user WHERE id>10 OR  id<2;
#AND
SELECT * FROM user WHERE id>0 AND id<5;
# BINARY 区分大小写，默认是不区分大小写的
SELECT * FROM user WHERE BINARY username='xxxxx';
#更新数据
UPDATE user SET username = '程启' WHERE id = 6;
#删除数据
DELETE FROM user WHERE id=6;
#like子句
SELECT * FROM user WHERE username LIKE '%李';
#排序 ASC(升序) DESC(降序)
SELECT * FROM user ORDER BY id DESC ;

#分组 查询
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl` (
  `id` int(11)  AUTO_INCREMENT,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `employee_tbl` VALUES ('1', '程起', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');
INSERT INTO employee_tbl (id,name,date) VALUES (1,'称启','2016-04-22 15:25:33');

COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
SELECT * FROM employee_tbl;
# group by
SELECT name, count(*) FROM   employee_tbl GROUP BY name;
#WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
#例如我们将以上的数据表按名字进行分组，再统计每个人登录的次数：
SELECT name, SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
#我们可以使用 coalesce 来设置一个可以取代 NUll 的名称，coalesce 语法：
#select coalesce(a,b,c); a==null 选b……类推
SELECT coalesce(name, '总数'), SUM(singin) as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;

#mysql进阶使用

# 连接的使用： JOIN
# INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
# LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
# RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
UPDATE employee_tbl set name = '程启' WHERE id = 2 ;
SELECT * FROM user INNER JOIN employee_tbl ON user.username = employee_tbl.name;
#等价
SELECT user.id,user.username,user.email FROM user, employee_tbl WHERE user.username = employee_tbl.name ORDER BY id DESC ;
# left join
SELECT * FROM user LEFT JOIN employee_tbl ON user.username = employee_tbl.name;
#right join
SELECT * FROM user RIGHT JOIN employee_tbl ON user.id = employee_tbl.id;
# 查询时空值处理：IS NULL、IS NOT NULL:

# 事务
# start transaction 或者 BEGIN 开始一个事务
# COMMIT; 提交
# savepoint 保存点名称

#ALTER命令 删除，添加或修改表字段
#删除字段
ALTER TABLE user DROP regIp;
#增加字段 FIRST:添加到第一个
ALTER  TABLE user ADD age INT(3) DEFAULT 0;
ALTER  TABLE user ADD age INT(3) DEFAULT 0 FIRST ;
#AFTER value 添加的字段在value后面
ALTER  TABLE user ADD age INT(3) DEFAULT 0 AFTER username;
#修改字段
#MODIFY
ALTER TABLE user MODIFY status CHAR;
#CHANGE
ALTER TABLE user CHANGE age userage INT ;
ALTER TABLE user CHANGE userage userage INT NOT NULL DEFAULT 100;
#修改字段默认值
ALTER TABLE user ALTER userage SET DEFAULT 100;
#删除字段默认值
ALTER TABLE user ALTER userage DROP DEFAULT;
#修改表的类型
#ALTER TABLE user TYPE = MYISAM;
#：查看数据表类型
SHOW TABLE STATUS LIKE 'user';
#修改表名
ALTER TABLE cloneuser RENAME TO clone_user;

#复制表 (INSERT INTO... SELECT)
SHOW TABLES ;
SHOW CREATE TABLE user;

CREATE TABLE `clone user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `email` varchar(255) NOT NULL COMMENT '用户邮箱',
  `password` varchar(255) NOT NULL COMMENT '用户密码',
  `user_name` varchar(255) DEFAULT NULL COMMENT '用户昵称',
  `role` varchar(255) DEFAULT 'normal' COMMENT '用户身份',
  `status` int(1) DEFAULT '0' COMMENT '用户状态',
  `register_time` datetime NOT NULL COMMENT '注册时间',
  `register_ip` varchar(255) DEFAULT NULL COMMENT '注册IP',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
# 复制
INSERT INTO clone_user  SELECT * from user;

#元数据
SELECT version();
SELECT database();
SELECT user();
SHOW STATUS ;
SHOW VARIABLES;

#查询重复数据
select user.user_name,count(*) as count from user group by user_name having count>1;
select * from user where user_name in (select user_name from user group by user_name having count(user_name) > 1);
#过滤重复数据
SELECT DISTINCT user_name  FROM user  ORDER BY user_name;
SELECT * FROM user GROUP BY (user.user_name);
# 删除重复数据
CREATE TABLE tmp SELECT *  FROM user GROUP BY (user_name);
DROP TABLE user;
ALTER TABLE tmp RENAME TO user;
SELECT * FROM SsmTestDB.user ORDER BY id ASC ;

#导出数据
SELECT * FROM SsmTestDB.user INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/user_db.txt';
#CSV格式
SELECT * FROM SsmTestDB.user INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/user_db2.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/user_db3.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM SsmTestDB.user;

#导出表
#-u root -p --no-create-info \--tab=/tmp W3CSCHOOL w3cschool_tbl

